﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using TCL.EO;
using TCL.FUNC;

namespace TCL.DB
{
    public class ShareDAO
    {
        public class FuncDAO : MSSqlDataProvider
        {
            //public override String sConnectionStrings { get { return "connMSSql"; } set { } }
            public FuncDAO(Boolean _bWriteLog = true, String _sConnectionStrings = ConstEx.connectionStrings.connMSSql_DF)
            {
                this.bWriteLog = _bWriteLog;
                this.sConnectionStrings = _sConnectionStrings;
            }

            public FuncEO SelectItem(Int32 _PK_iFunctionID)
            {
                SqlCommand cmd = new SqlCommand("Func_SelectItem");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PK_iFunctionID", _PK_iFunctionID));
                return FuncDO(GetDataTable(cmd));
            }

            public DataTable SelectList()
            {
                SqlCommand cmd = new SqlCommand("Func_SelectList");
                cmd.CommandType = CommandType.StoredProcedure;
                return GetDataTable(cmd);
            }

            public List<FuncEO> SelectBy_iAppID_iParentID(Int32 _iAppID, Int32 _iParentID)
            {
                SqlCommand cmd = new SqlCommand("Func_SelectBy_iAppID_iParentID");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@iAppID", _iAppID));
                cmd.Parameters.Add(new SqlParameter("@iParentID", _iParentID));
                return FuncDO_EX(GetDataTable(cmd));
            }

            public SqlDataReader SelectBy_iAppID_iParentID_EX(Int32 _iAppID, Int32 _iParentID)
            {
                SqlCommand cmd = new SqlCommand("Func_SelectBy_iAppID_iParentID");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@iAppID", _iAppID));
                cmd.Parameters.Add(new SqlParameter("@iParentID", _iParentID));
                return GetReadOnlyData(cmd);
            }

            public FuncEO FuncDO(DataTable input)
            {
                List<FuncEO> lst = new List<FuncEO>();
                lst = (List<FuncEO>)ConvertEx.DataTableEx.DataTable_2_ListObject<FuncEO>(input, lst.GetType());
                if (CheckEx.IsListObject(lst))
                    return lst[0];
                else
                    return null;
            }

            public List<FuncEO> FuncDO_EX(DataTable input)
            {
                List<FuncEO> lst = new List<FuncEO>();
                lst = (List<FuncEO>)ConvertEx.DataTableEx.DataTable_2_ListObject<FuncEO>(input, lst.GetType());
                return lst;
            }

            public Boolean Insert_Update_Delete(FuncEO _FuncEO, Int16 Insert_Update_Delete)
            {
                if (Insert_Update_Delete != 3)
                    _FuncEO.sToken = SecurityEx.getTokenFunc(_FuncEO, false);

                SqlCommand cmd = new SqlCommand("Func_Insert_Update_Delete");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PK_iFunctionID", _FuncEO.PK_iFunctionID));
                cmd.Parameters.Add(new SqlParameter("@sName", NullEx.getNullValue(_FuncEO.sName)));
                cmd.Parameters.Add(new SqlParameter("@sPath", NullEx.getNullValue(_FuncEO.sPath)));
                cmd.Parameters.Add(new SqlParameter("@sToken", NullEx.getNullValue(_FuncEO.sToken)));
                cmd.Parameters.Add(new SqlParameter("@iAppID", NullEx.getNullValue(_FuncEO.iAppID)));
                cmd.Parameters.Add(new SqlParameter("@iParentID", NullEx.getNullValue(_FuncEO.iParentID)));
                cmd.Parameters.Add(new SqlParameter("@iRole", NullEx.getNullValue(_FuncEO.iRole)));
                cmd.Parameters.Add(new SqlParameter("@bStatus", NullEx.getNullValue(_FuncEO.bStatus)));
                cmd.Parameters.Add(new SqlParameter("@Insert_Update_Delete", Insert_Update_Delete));
                return ExecuteNonQuery_RetBool(cmd, true);
            }
        }

        public class UserDAO : MSSqlDataProvider
        {
            //public override String sConnectionStrings { get { return "connMSSql"; } set { } }
            public UserDAO(Boolean _bWriteLog = true, String _sConnectionStrings = ConstEx.connectionStrings.connMSSql_DF)
            {
                this.bWriteLog = _bWriteLog;
                this.sConnectionStrings = _sConnectionStrings;
            }

            /// <summary>
            /// CheckExists
            /// </summary>
            /// <param name="sInput">sEmail, sUsername</param>
            /// <param name="iType">1:Email, 2:Username</param>
            /// <returns></returns>
            public Boolean CheckExists(String sInput, Int16 iType)
            {
                SqlCommand cmd = new SqlCommand("Account_CheckExists");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@sInput", sInput));
                cmd.Parameters.Add(new SqlParameter("@iType", iType));
                return ExecuteNonQuery_RetBool(cmd, true);
            }

            public Boolean Insert_Update_Delete(AccountEO _AccountEO, Int16 Insert_Update_Delete)
            {
                SqlCommand cmd = new SqlCommand("Account_Insert_Update_Delete");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@sUsername", _AccountEO.sUsername));
                cmd.Parameters.Add(new SqlParameter("@sPassword", _AccountEO.sPassword));
                cmd.Parameters.Add(new SqlParameter("@sEmail", _AccountEO.sEmail));
                cmd.Parameters.Add(new SqlParameter("@sFullName", _AccountEO.sFullName));
                cmd.Parameters.Add(new SqlParameter("@sAddress", _AccountEO.sAddress));
                cmd.Parameters.Add(new SqlParameter("@sPhoneNumber", _AccountEO.sPhoneNumber));
                cmd.Parameters.Add(new SqlParameter("@sPermission", NullEx.getNullValue(_AccountEO.sPermission)));
                cmd.Parameters.Add(new SqlParameter("@iPermission", _AccountEO.iPermission));
                cmd.Parameters.Add(new SqlParameter("@tDateOfBirth", _AccountEO.tDateOfBirth));
                cmd.Parameters.Add(new SqlParameter("@Insert_Update_Delete", Insert_Update_Delete));
                _AccountEO.PK_iAccountID = ExecuteNonQuery_RetInt32(cmd);
                if (_AccountEO.PK_iAccountID > 0)
                    return true;
                else
                    return false;
            }

            public Boolean Update_Permission(AccountEO _AccountEO)
            {
                SqlCommand cmd = new SqlCommand("Account_Update_Permission");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PK_iAccountID", _AccountEO.PK_iAccountID));
                cmd.Parameters.Add(new SqlParameter("@sPermission", _AccountEO.sPermission));
                cmd.Parameters.Add(new SqlParameter("@iPermission", _AccountEO.iPermission));
                _AccountEO.PK_iAccountID = ExecuteNonQuery_RetInt32(cmd);
                if (_AccountEO.PK_iAccountID > 0)
                    return true;
                else
                    return false;
            }

            public AccountEO SelectItem(Int32 _PK_iAccountID, String _sUsername)
            {
                SqlCommand cmd = new SqlCommand("Account_SelectItem");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PK_iAccountID", NullEx.getNullValue(_PK_iAccountID)));
                cmd.Parameters.Add(new SqlParameter("@sUsername", NullEx.getNullValue(_sUsername)));
                return AccountDO(GetDataTable(cmd));
            }

            public Boolean SelectBy_sPermission(Object sSecret, String sUrl, Int32 iFuncID)
            {
                try
                {
                    if ((sSecret == null) || (String.IsNullOrEmpty(StringEx.Trim(sUrl))) || (iFuncID == null))
                        return false;
                    String strCookie = SecurityEx.Login.Decrypt(Convert.ToString(sSecret), SecurityEx.Base64Encode(System.Web.HttpContext.Current.Request.ServerVariables["remote_addr"]));
                    FormsAuthenticationTicket ticket = FormsAuthentication.Decrypt(strCookie);
                    //LoginEO _LoginEO = new LoginEO();
                    //_LoginEO = AccountDAO.getPK_iAccountID(Convert.ToString(sSecret));
                    //if (_LoginEO == null)
                    //    return false;
                    //if (_LoginEO.PK_iAccountID == 0)
                    //    return false;
                    if (System.Web.HttpContext.Current.Request.Cookies["isTest"] == null && StringEx.Equals(ticket.Name, StringEx.Trim(SystemEx.getAppSettings("UserTest"))))
                    {
                        HttpCookie cookie = new HttpCookie("isTest");
                        cookie.Value = SecurityEx.Token.getToken(new TokenEO(ConstEx.Token_iType_C.Test, DateTime.Now.AddDays(1), ticket.Name));
                        cookie.Expires = DateTime.Now.AddDays(1);
                        System.Web.HttpContext.Current.Response.Cookies.Add(cookie);
                    }
                    sUrl = OtherEx.RemoveDomainInUrl(sUrl);
                    SqlCommand cmd = new SqlCommand("Account_SelectBy_sPermission");
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@PK_iAccountID", ticket.Version));
                    cmd.Parameters.Add(new SqlParameter("@sUrl", sUrl));
                    cmd.Parameters.Add(new SqlParameter("@iFuncID", iFuncID));
                    return ExecuteNonQuery_RetBool(cmd, true);
                }
                catch (Exception ex)
                {
                    (new Logs()).WriteLog_To_DB(new LogEO(ex.ToString(), ConstEx.Log_iType.EXCEPTION), false);
                    return false;
                }
            }

            public DataTable SelectBy_bState(String _bState)
            {
                SqlCommand cmd = new SqlCommand("Account_SelectBy_bState");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@bState", NullEx.getNullValue(_bState)));
                return GetDataTable(cmd);
            }

            public DataSet Login(AccountEO _AccountEO)
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand("Account_Login");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@sUsername", _AccountEO.sUsername));
                cmd.Parameters.Add(new SqlParameter("@sPassword", _AccountEO.sPassword));
                cmd.Parameters.Add(new SqlParameter("@iPermission", _AccountEO.iPermission));
                cmd.Parameters.Add(new SqlParameter("@bState", _AccountEO.bState));
                return GetDataSet(cmd);
            }

            public static FormsAuthenticationTicket getPK_iAccountID(Object sSecret)
            {
                FormsAuthenticationTicket ticket = null;
                if (sSecret != null)
                {
                    try
                    {
                        String strCookie = SecurityEx.Login.Decrypt(Convert.ToString(sSecret), SecurityEx.Base64Encode(System.Web.HttpContext.Current.Request.ServerVariables["remote_addr"]));
                        ticket = FormsAuthentication.Decrypt(strCookie);

                        //String strCookie = Security.Login.Decrypt(cookie, Security.Base64Encode(System.Web.HttpContext.Current.Request.ServerVariables["remote_addr"]));
                        //_LoginEO = (LoginEO)Commons.Json2Object(strCookie, _LoginEO.GetType());
                    }
                    catch
                    {
                        ticket = new FormsAuthenticationTicket("", false, 0);
                    }
                }
                return ticket;
            }

            public AccountEO AccountDO(DataTable input)
            {
                List<AccountEO> lst = new List<AccountEO>();
                lst = (List<AccountEO>)ConvertEx.DataTableEx.DataTable_2_ListObject<AccountEO>(input, lst.GetType());
                if (CheckEx.IsListObject(lst))
                    return lst[0];
                else
                    return null;
            }
        }

        public class LogDAO : SQLiteDataProvider
        {
            public override Boolean bWriteLog { get { return false; } set { } }
            public override String sConnectionStrings { get { return ConstEx.connectionStrings.connSqLite_LOG; } set { } }

            public LogDAO(Boolean _bWriteLog = true, String _sConnectionStrings = ConstEx.connectionStrings.connSqLite_LOG)
            {
                this.bWriteLog = _bWriteLog;
                this.sConnectionStrings = _sConnectionStrings;
            }

            public Boolean Log_CreateTable(String TableName)
            {
                String sQuery = String.Format(ConstEx.QuerySqlite.Log_CreateTable, TableName);
                SqlCommand cmd = new SqlCommand(sQuery);
                cmd.CommandType = CommandType.Text;
                return ExecuteNonQuery(cmd);
            }

            public void Log_InsertItem(LogEO objLog)
            {
                //Log_CreateTable("tblLog");
                //Log_CreateTable("tblLogAll");
                String sQuery = String.Format(ConstEx.QuerySqlite.Log_InsertItem, StringEx.Remove_Special_Character_SQLite(objLog.sContent), StringEx.Remove_Special_Character_SQLite(objLog.sUrl), StringEx.Remove_Special_Character_SQLite(objLog.sUrlRef), StringEx.Remove_Special_Character_SQLite(objLog.sIP), StringEx.Remove_Special_Character_SQLite(objLog.sDatabase), objLog.iType, objLog.dProcessTime, objLog.bStatus ? "1" : "0", objLog.tTimeBegin.ToString("yyyy-MM-dd HH:mm:ss.fff"), objLog.tTimeEnd.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                sQuery = NullEx.getNullValueSQLite(sQuery);
                using (SqlCommand cmd = new SqlCommand(sQuery))
                {
                    cmd.CommandType = CommandType.Text;
                    String sTemp = Convert.ToString(ExecuteScalar(cmd));
                    Int32 iRowCount = 0;
                    Int32.TryParse(sTemp, out iRowCount);
                    if (iRowCount > Convert.ToInt32(SystemEx.getAppSettings("NMT_LogSize", "199")))
                    {
                        sQuery = String.Format(ConstEx.QuerySqlite.LogAll_InsertItem_When_MaxSize, "tblLogAll", "tblLog", "tblLog");
                        sQuery = NullEx.getNullValueSQLite(sQuery);
                        SqlCommand cmd2 = new SqlCommand(sQuery);
                        cmd2.CommandType = CommandType.Text;
                        ExecuteNonQuery(cmd2);
                        //SplitTable("tblLog");
                    }
                }
            }

            public Boolean LogOther_InsertItem(LogEO objLog)
            {
                Log_CreateTable("tblLogOther");
                String sQuery = String.Format(ConstEx.QuerySqlite.LogOther_InsertItem, objLog.sContent, objLog.sUrl, objLog.sUrlRef, objLog.sIP, objLog.sDatabase, objLog.iType, objLog.dProcessTime, objLog.bStatus ? "1" : "0", objLog.tTimeBegin.ToString("yyyy-MM-dd HH:mm:ss.fff"), objLog.tTimeEnd.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                sQuery = NullEx.getNullValueSQLite(sQuery);
                SqlCommand cmd = new SqlCommand(sQuery);
                cmd.CommandType = CommandType.Text;
                return ExecuteNonQuery(cmd);
            }

            public DataTable Log_SelectBy_TableName(String sTableName)
            {
                String sQuery = String.Format(ConstEx.QuerySqlite.Log_SelectBy_TableName, sTableName);
                SqlCommand cmd = new SqlCommand(sQuery);
                cmd.CommandType = CommandType.Text;
                return GetDataTable(cmd);
            }

            public DataTable Database_SelectAllTableName()
            {
                SqlCommand cmd = new SqlCommand(ConstEx.QuerySqlite.Database_SelectAllTableName);
                cmd.CommandType = CommandType.Text;
                return GetDataTable(cmd);
            }

            public Boolean Log_DropTable(String sTableName)
            {
                String sQuery = String.Format(ConstEx.QuerySqlite.Log_DropTable, sTableName);
                SqlCommand cmd = new SqlCommand(sQuery);
                cmd.CommandType = CommandType.Text;
                return ExecuteNonQuery(cmd);
            }

            public Boolean Log_DeleteDataTable(String sTableName)
            {
                String sQuery = String.Format(ConstEx.QuerySqlite.Log_DeleteDataTable, sTableName);
                SqlCommand cmd = new SqlCommand(sQuery);
                cmd.CommandType = CommandType.Text;
                return ExecuteNonQuery(cmd);
            }
        }
    }
}
